package com.briup.util;

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
import java.util.function.Function;

public class JdbcUtil {
    // 定义连接池的数据源
    private static DataSource dataSource;
    // 给普通的连接对象提供数据
    private static Properties properties;

    // 准备静态代码块，⽤来读取配置⽂件
    static {
        properties = new Properties();
        InputStream is = JdbcUtil.class.getClassLoader().getResourceAsStream("druid.properties");
        try {
            properties.load(is);
            dataSource = DruidDataSourceFactory.createDataSource(properties);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /*** 使⽤druid连接池获取的连接对象
     * @return
     * @throws SQLException
     */
    public static Connection
    getDruidConnection() throws SQLException {
        return dataSource.getConnection();
    }

    /**
     * 传统⽅式获得连接对象
     *
     * @return
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public static Connection getConnection() throws ClassNotFoundException, SQLException {
        // 准备4个参数内容
        String driverClassName = properties.getProperty("driverClassName");
        String url = properties.getProperty("url");
        String username = properties.getProperty("username");
        String password = properties.getProperty("password");
        Class.forName(driverClassName);
        return DriverManager.getConnection(url, username, password);
    }

    /**
     * 获取可以设置是否⾃动提交的连接对象
     *
     * @param autoCommit
     * @return
     * @throws SQLException
     */
    public static Connection
    getConnection(boolean autoCommit) throws SQLException {
        Connection conn = getDruidConnection();
        conn.setAutoCommit(autoCommit);
        return conn;
    }

    /**
     * 带结果集的（DQL）的关闭⽅法
     *
     * @param rs
     * @param st
     * @param conn
     */
    public static void close(ResultSet rs, Statement st, Connection conn) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (st != null) {
            try {
                st.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /*** 不带结果集的关闭⽅法，适⽤于DQL之外的所有
     sql
     * @param st
     * @param conn
     */
    public static void close(Statement st, Connection conn) {
        close(null, st, conn);
    }

    /**
     * 特别⽤来执⾏update语句
     *
     * @param sql
     * @return
     */
    public static int executeUpdate(String sql) {
        Connection conn = null;
        Statement st = null;
        // 定义⼀个值⽤来返回
        int row = -1;
        try {
            conn = getDruidConnection();
            st = conn.createStatement();
            row = st.executeUpdate(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(st, conn);
        }
        return row;
    }

    /**
     * execute⽅法的封装，适⽤于delete，
     * insert，DDL
     *
     * @param sql
     * @return
     */
    public static boolean execute(String sql) {
        Connection conn = null;
        Statement st = null;
        // 定义⼀个值⽤来返回
        boolean flag = false;
        try {
            conn = getDruidConnection();
            st = conn.createStatement();
            flag = st.execute(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(st, conn);
        }
        return flag;
    }

    /**
     * 根据转换规则function，查询语句之后，处理
     * 得到对应的T类型，查询结果只能是⼀⾏记录或者没有记录
     *
     * @param sql      查询语句
     * @param function 结果集处理⽅案
     * @param <T>      封装成对应的类型
     * @return
     */
    public static <T> T
    queryForObject(String sql, Function<ResultSet, T> function) {
        return queryForList(sql, function).get(0);
    }

    /**
     * 查询多条记录，通过规则function，转换成对
     * 应的list
     *
     * @param sql
     * @param function
     * @param <T>
     * @return
     */
    public static <T> List<T>
    queryForList(String sql, Function<ResultSet, T> function) {
        List<T> list = new ArrayList<>();
        T t = null;
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            conn = getDruidConnection();
            st = conn.createStatement();
            rs = st.executeQuery(sql);
            // 接下来的处理不同
            while (rs.next()) {
                t = function.apply(rs);
                list.add(t);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(rs, st, conn);
        }
        return list;
    }

    /**
     * 根据类型的class对象，查询多个结果
     *
     * @param sql
     * @param clazz
     * @param <T>
     * @return
     */
    public static <T> List<T>
    selectMany(String sql, Class<T> clazz) {
        T t = null;
        List<T> list = new ArrayList<>();
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            conn = getDruidConnection();
            st = conn.createStatement();
            rs = st.executeQuery(sql);
            // 处理结果集
            List<TypeAndName> tanList = parse(clazz);
            while (rs.next()) {
             // 创建t对象
                t = clazz.newInstance();
                // 遍历tanList获取类型
                for (TypeAndName tan :
                        tanList) {
                    if ("long".equals(tan.type)) {
                        // 通过结果集获得对应的值
                        long id = rs.getLong("id");
                        // 调⽤对应对象的set⽅法
                        tan.invokeSet(t, long.class, id);
                    } else if ("String".equals(tan.type)) {
                        String name = rs.getString("name");
                        tan.invokeSet(t, String.class, name);
                    } else if ("int".equals(tan.type)) {
                        int age = rs.getInt("age");
                        tan.invokeSet(t, int.class, age);
                    } else if ("Date".equals(tan.type)) {
                        java.util.Date dob = rs.getDate("dob");
                        tan.invokeSet(t, java.util.Date.class, dob);
                    }
                }
                list.add(t);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } finally {
            close(rs, st, conn);
        }
        return list;
    }

    public static <T> T selectOne(String sql, Class<T> clazz) {
        return selectMany(sql, clazz).get(0);
    }

    private static <T> List<TypeAndName>
    parse(Class<T> clazz) {
        // 使⽤反射，获得⼀个类中所有的属性对象
        Field[] fields = clazz.getDeclaredFields();
        // 准备⼀个集合⽤来返回
        List<TypeAndName> list = new ArrayList<>();
        // 遍历数组
        for (Field f : fields) {
            String fieldName = f.getName();
            String fieldType = f.getType().getSimpleName();
            TypeAndName tan = new TypeAndName(fieldType, fieldName);
            list.add(tan);
        }
        return list;
    }

    // 定义⼀个静态私有的内部类，TypeAndName
    // 获得⼀个类型中属性的类型和名字
    private static class TypeAndName {
        String type;
        String name;

        public TypeAndName(String type, String name) {
            this.type = type;
            this.name = name;
        }

        @Override
        public String toString() {
            return "TypeAndName{" +
                    "type='" + type + '\''
                    + ", name='" + name +
                    '\'' +
                    '}';
        }

        public <T> void invokeSet(Object obj, Class<T> c, Object value) {
            // 定义set⽅法的名字
            String setMethodName = "set" + initCap(name);
            try {
                // 获取set⽅法的对象
                Method method = obj.getClass().getDeclaredMethod(setMethodName, c);
                // 执⾏set⽅法
                method.invoke(obj, value);
            } catch (NoSuchMethodException
                    e) {
                e.printStackTrace();
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            } catch (InvocationTargetException e) {
                e.printStackTrace();
            }
        }

        /**
         * ⾸字⺟⼤写的⽅法
         *
         * @param str
         * @return
         */
        private String initCap(String str) {
            return str.substring(0, 1).toUpperCase() + str.substring(1);
        }
    }
}